﻿#=============================================================================
#   File: DiskSpdAnalyze.ps1 
#
#   Summary: This script parses the results information from the DiskSpd too
#   and creates an Excel workbook with charts for the data.
#
#   Date: August 24, 2015
#
#   PowerShell Versions:
#         4.0
#-----------------------------------------------------------------------------
#   Copyright (C) 2015 Louis Li
#   All rights reserved. 
#
#
#   You may alter this code for your own *non-commercial* purposes. You may
#   republish altered code as long as you give due credit. 
#
#
#   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
#   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
#   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
#   PARTICULAR PURPOSE. 
#   
#   Special thanks for Jonathan Kehayias for his execellent blog - 
#   "Parsing SQLIO Output to Excel Charts using Regex in PowerShell"
#   https://www.sqlskills.com/blogs/jonathan/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell/
#
#=============================================================================
param(	[Parameter(Mandatory=$TRUE)]
		[ValidateNotNullOrEmpty()]
		[string]
		$FileName)

function Populate-WorkSheet
(
    $Worksheet,
    $Operation,
    $IOType
)
{
$x = 2
$Worksheet.Cells.Item(1,1) = "Drive"
$Worksheet.Cells.Item(1,2) = "Threads"
$Worksheet.Cells.Item(1,3) = "Operation"
$Worksheet.Cells.Item(1,4) = "Duration"
$Worksheet.Cells.Item(1,5) = "IOSize"
$Worksheet.Cells.Item(1,6) = "IOType"
$Worksheet.Cells.Item(1,7) = "PendingIO"
$Worksheet.Cells.Item(1,8) = "FileSize"
$Worksheet.Cells.Item(1,9) = "IOPS"
$Worksheet.Cells.Item(1,10) = "MBs/Sec"
$Worksheet.Cells.Item(1,11) = "Avg_Lat(ms)"
$Worksheet.Cells.Item(1,12) = "CPU"
$Worksheet.Cells.Item(1,13) = "Caption"

#Sequential Read  
$Results | ? {($_.Operation -eq $Operation) -and ($_.IOType -eq $IOType ) } |% {
    $Worksheet.Cells.Item($x,1) = $_.Drive
	$Worksheet.Cells.Item($x,2) = $_.Threads
	$Worksheet.Cells.Item($x,3) = $_.Operation
	$Worksheet.Cells.Item($x,4) = $_.Duration
	$Worksheet.Cells.Item($x,5) = $_.IOSize
	$Worksheet.Cells.Item($x,6) = $_.IOType
	$Worksheet.Cells.Item($x,7) = $_.PendingIO
	$Worksheet.Cells.Item($x,8) = $_.FileSize
	$Worksheet.Cells.Item($x,9) = $_.IOPS
	$Worksheet.Cells.Item($x,10) = $_.MBs_Sec
	$Worksheet.Cells.Item($x,11) = $_.AvgLat_ms
	$Worksheet.Cells.Item($x,12) = $_.CPU
	$Worksheet.Cells.Item($x,13) = [string]$_.IOSize + "KB " + [string]$_.IOType + " " + `
								[string]$_.Operation + " " + [string]$_.Threads + `
								" Threads " + [string]$_.PendingIO + " pending"
	$x++}
}

function Add-Chart
(
    $WorkSheet_SR,
    $WorkSheet_RR,
    $WorkSheet_SW,
    $WorkSheet_RW,
    $ChartName,
    $xColumn,
    $Column
)
{
$WorkBook.Charts.Add() | Out-Null
$Chart = $WorkBook.ActiveChart
$Chart.ChartType = 4
$xRange = $xColumn + "2:" + $xColumn + $x
$Range = $Column + "2:" + $Column + $x
$Chart.SetSourceData($WorkSheet_SR.Range($Range))
$Chart.SeriesCollection(1).xValues = $WorkSheet_SR.Range($xRange)
$Chart.SeriesCollection(1).Name = 'Sequential Read'
$Chart.SeriesCollection(1).Format.Line.Weight = $LineWidth
$Chart.SeriesCollection().NewSeries.Invoke() | Out-Null
$Chart.SeriesCollection(2).xValues = $WorkSheet_RR.Range($xRange)
$Chart.SeriesCollection(2).Values = $WorkSheet_RR.Range($Range)
$Chart.SeriesCollection(2).Name = 'Random Read'
$Chart.SeriesCollection(2).Format.Line.Weight = $LineWidth
$Chart.SeriesCollection().NewSeries.Invoke() | Out-Null
$Chart.SeriesCollection(3).xValues = $WorkSheet_SW.Range($xRange)
$Chart.SeriesCollection(3).Values = $WorkSheet_SW.Range($Range)
$Chart.SeriesCollection(3).Name = 'Sequential Write'
$Chart.SeriesCollection(3).Format.Line.Weight = $LineWidth
$Chart.SeriesCollection().NewSeries.Invoke() | Out-Null
$Chart.SeriesCollection(4).xValues = $WorkSheet_RW.Range($xRange)
$Chart.SeriesCollection(4).Values = $WorkSheet_RW.Range($Range)
$Chart.SeriesCollection(4).Name = 'Random Write'
$Chart.SeriesCollection(4).Format.Line.Weight = $LineWidth
$Chart.Name = $ChartName

}

$filedata = [string]::Join([Environment]::NewLine,(Get-Content $FileName))
$LineWidth = 5

$Results = $filedata.Split( [String[]]"Command Line:", [StringSplitOptions]::RemoveEmptyEntries ) | 
    ? {[regex]::Match($_,"diskspd").Success -eq $true} | 
    select @{Name="Drive"; Expression={([regex]::Match($_, "path:\s\'(\w):").Groups[1].Value)}},`
                @{Name="Threads"; Expression={[int]([regex]::Match($_, "thread count:(\s)+(\d+)").Groups[2].Value)}},`
				@{Name="Operation"; Expression={[regex]::Match($_, "performing\s(\w+)\stest").Groups[1].Value}},`
				@{Name="Duration"; Expression={[int]([regex]::Match($_, "duration:\s(\d+)").Groups[1].Value)}},`
				@{Name="IOSize"; Expression={([int][regex]::Match($_, "(\s)*block size:(\s)+(\d+)").Groups[3].Value)/1024}},`
				@{Name="IOType"; Expression={[regex]::Match($_, "using\sFILE_FLAG_(\w+)_").Groups[1].Value}},`
				@{Name="PendingIO"; Expression={[int]([regex]::Match($_, "number\sof\soutstanding\sI\/O\soperations:\s(\d+)").Groups[1].Value)}},`
				@{Name="FileSize"; Expression={[int]([regex]::Match($_, "\s\((\d+)MB\)").Groups[1].Value)}},`
				@{Name="IOPS"; Expression={([regex]::Match($_, "total:(.*)").Groups[1].Value).Split('|')[3].Trim()}},`
				@{Name="MBs_Sec"; Expression={([regex]::Match($_, "total:(.*)").Groups[1].Value).Split('|')[2].Trim()}},`
				@{Name="AvgLat_ms"; Expression={([regex]::Match($_, "total:(.*)").Groups[1].Value).Split('|')[4].Trim()}},`
                @{Name="CPU"; Expression={([regex]::Match($_, "avg.(.*)").Groups[1].Value).Split('|')[1]}} `
	 | Sort-Object Drive,IOSize, IOType, Operation, Threads 

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$WorkBook = $Excel.WorkBooks.Add()
$WorkBook.WorkSheets.Item(1).Name = "Data_SeqRd"

#Louis Li: Newer version of Excel has only 1 sheet by default
for ($sheetid = 2; $sheetid -le $Workbook.WorkSheets.Count; $sheetid++)
{
    $WorkBook.WorkSheets.Item($sheetid).Delete();
}

$WorkSheet_SR = $WorkBook.WorkSheets.Item("Data_SeqRd")
. Populate-WorkSheet $WorkSheet_SR 'read' 'SEQUENTIAL'


#Random Read 
$WorkSheet_RR = $WorkBook.WorkSheets.Add()
$WorkSheet_RR.Name = 'Data_RndRd'
. Populate-WorkSheet $WorkSheet_RR 'read' 'RANDOM'

#Sequential Write 
$WorkSheet_SW = $WorkBook.WorkSheets.Add()
$WorkSheet_SW.Name = 'Data_SeqWr'
. Populate-WorkSheet $WorkSheet_SW 'write' 'SEQUENTIAL'


#Random Write 
$WorkSheet_RW = $WorkBook.WorkSheets.Add()
$WorkSheet_RW.Name = 'Data_RndWr'
. Populate-WorkSheet $WorkSheet_RW 'write' 'RANDOM'

$x--

#Start drawing chart
#Latency
. Add-Chart $WorkSheet_SR $WorkSheet_RR $WorkSheet_SW $WorkSheet_RW "IO Latency" "M" "K"

#IOPS
. Add-Chart $WorkSheet_SR $WorkSheet_RR $WorkSheet_SW $WorkSheet_RW "IOPS" "M" "I"

#Throughput
. Add-Chart $WorkSheet_SR $WorkSheet_RR $WorkSheet_SW $WorkSheet_RW "MBs Sec" "M" "J"

